package com.habdqn.easybuy.dao.impl;

import com.habdqn.easybuy.dao.BaseDao;
import com.habdqn.easybuy.dao.ProductDao;
import com.habdqn.easybuy.entity.Product;
import com.habdqn.easybuy.util.Page;
import com.habdqn.easybuy.util.StringUtils;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * @author Xing.Jiejian
 * @date 2020/11/30
 */
public class ProductDaoImpl extends BaseDao implements ProductDao {
    private String baseSelect = "SELECT id, name, description, price, stock, category_id, file_name FROM product";

    /**
     * 通过ID查询单条数据
     *
     * @param id 主键
     * @return 实例对象
     */
    @Override
    public Product queryById(Integer id) {
        String sql = baseSelect + " WHERE id=?";
        List<Product> products = getListByRS(executeQuery(sql, id));
        if (products != null && products.size() > 0) {
            return products.get(0);
        }
        return null;
    }

    /**
     * 通过实体作为筛选条件查询
     *
     * @param product 实例对象
     * @param page    分页对象
     * @return 对象列表
     */
    @Override
    public List<Product> queryAll(Product product, Page<Product> page) {
        StringBuilder sql = new StringBuilder(baseSelect);
        List<Object> paramList = buildQueryConditions(product, sql);
        buildPageQuery(sql, paramList, page);
        return getListByRS(executeQuery(sql.toString(), paramList.toArray()));
    }

    /**
     * 根据分类Id查询分页商品集合
     *
     * @param categoryIds 分类Id，多个之间用","隔开
     * @param page        分页对象
     * @return 分页集合
     */
    @Override
    public List<Product> queryAll(String categoryIds, Page<Product> page) {
        StringBuilder sql = new StringBuilder(baseSelect).append(" WHERE category_id IN (").append(categoryIds).append(")");
        List<Object> paramList = new ArrayList<>();
        buildPageQuery(sql, paramList, page);
        return getListByRS(executeQuery(sql.toString(), paramList.toArray()));
    }

    /**
     * 新增数据
     *
     * @param product 实例对象
     * @return 影响行数
     */
    @Override
    public int insert(Product product) {
        String sql = "INSERT INTO product(name, description, price, stock, category_id, file_name) VALUES(?, ?, ?, " +
                "?, ?, ?)";
        Object[] params = {product.getName(), product.getDescription(), product.getPrice(), product.getStock(),
                product.getCategoryId(), product.getFileName()};
        return executeInsert(sql, params);
    }

    /**
     * 修改数据
     *
     * @param product 实例对象
     * @return 影响行数
     */
    @Override
    public int update(Product product) {
        String sql = "UPDATE product SET name=?, description=?, price=?, stock=?, category_id=?, file_name=? WHERE id=?";
        Object[] params = {product.getName(), product.getDescription(), product.getPrice(), product.getStock(),
                product.getCategoryId(), product.getFileName(), product.getId()};
        return executeUpdate(sql, params);
    }

    /**
     * 通过主键删除数据
     *
     * @param id 主键
     * @return 影响行数
     */
    @Override
    public int deleteById(Integer id) {
        String sql = "DELETE FROM product WHERE id=?";
        return executeUpdate(sql, id);
    }

    /**
     * 查询记录数
     *
     * @param product 查询条件
     * @return 记录数
     */
    @Override
    public int getTotal(Product product) {
        StringBuilder sql = new StringBuilder(" SELECT COUNT(id) FROM product ");
        List<Object> paramList = buildQueryConditions(product, sql);
        return (int) (long) uniqueResult(sql.toString(), paramList.toArray());
    }


    /**
     * 查询记录数
     *
     * @param categoryIds 分类编号
     * @return 记录数
     */
    @Override
    public int getTotal(String categoryIds) {
        String sql = "SELECT COUNT(id) FROM product WHERE category_id IN ("+categoryIds+")";
        return (int) (long) uniqueResult(sql);
    }

    /**
     * 组装关于User的查询条件
     *
     * @param product 查询条件构成的User对象
     * @param sql     原始查询SQL
     * @return 参数集合
     */
    private List<Object> buildQueryConditions(Product product, StringBuilder sql) {
        List<Object> paramList = new ArrayList<>();
        if (product != null) {
            sql.append(" WHERE 1=1 ");
            if (product.getId() != null && product.getId() > 0) {
                sql.append(" AND id=? ");
                paramList.add(product.getId());
            }
            // 用户名模糊查询
            if (StringUtils.isNotBlank(product.getName())) {
                sql.append(" AND name LIKE ? ");
                paramList.add("%" + product.getName() + "%");
            }
            // 商品描述不查询
            // 价格
            if (product.getPrice() != null) {
                sql.append(" AND product=? ");
                paramList.add(product.getPrice());
            }
            // 库存
            if (product.getStock() != null) {
                sql.append(" AND stock=? ");
                paramList.add(product.getStock());
            }
            // 分类编号
            if (product.getCategoryId() != null) {
                sql.append(" AND category_id=? ");
                paramList.add(product.getCategoryId());
            }
            // 图片不查询
        }
        return paramList;
    }

    /**
     * 从结果集中提取对象集合
     *
     * @param rs 结果集
     * @return 用户集合
     */
    private List<Product> getListByRS(ResultSet rs) {
        List<Product> products = null;
        try {
            if (rs != null) {
                products = new ArrayList<>();
                while (rs.next()) {
                    Product product = new Product();
                    product.setId(rs.getInt("id"));
                    product.setName(rs.getString("name"));
                    product.setDescription(rs.getString("description"));
                    product.setPrice(rs.getDouble("price"));
                    product.setStock(rs.getInt("stock"));
                    product.setCategoryId(rs.getInt("category_id"));
                    product.setFileName(rs.getString("file_name"));
                    products.add(product);
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeAll();
        }
        return products;
    }
}
